package aole.excel;

import java.awt.BorderLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.FocusEvent;
import java.awt.event.FocusListener;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;

import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JComponent;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTabbedPane;
import javax.swing.JTextField;
import javax.swing.SpringLayout;
import javax.swing.SwingUtilities;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

import com.toedter.calendar.JDateChooser;

public class ExcelEntry extends JFrame implements ActionListener,
		ChangeListener, WindowListener, FocusListener {
	private static final long		serialVersionUID	= 1L;
	private static final long		MILLISECS_PER_DAY	= 86400000;
	// fields
	private static final String		HEADERS[]			= { "Text0", "Date1",
			"Date2", "Date3", "Date4", "Date5", "Text1", "Text2", "Text3",
			"Text4", "Text5"							};
	private static final int		DATATYPES[]			= { 1, 2, 2, 2, 2, 2,
			1, 1, 1, 1, 1,								};
	private JComponent				entryFields[]		= new JComponent[HEADERS.length];
	private JComponent				recordFields[]		= new JComponent[HEADERS.length];
	private String[]				headerLabels		= new String[HEADERS.length];
	private JComboBox< String >		srhcombo;
	private JTextField				srhdiff;
	private JLabel					recordLabel;
	private JButton					navFirst;
	private JButton					navPrev;
	private JButton					navNext;
	private JButton					navLast;
	private JCheckBox				filterByDate;
	// filter by text search?
	private JCheckBox				filterByText;
	// list of text fields
	private JComboBox< Object >		srhtextcombo;
	// text to search text box
	private JTextField				srhText;
	private JComboBox< String >		lblCombo;

	// excel
	private File					file;
	private HSSFWorkbook			workbook;
	private HSSFSheet				sheet;
	private int						insertionPoint;
	private HSSFCellStyle			dateStyle;
	private ArrayList< Integer >	srchInd				= new ArrayList<>();
	private int						cursor;
	private Properties				properties;
	private JTextField				txtLabel;
	private JPanel					listPane;
	private JButton					recUpdate;

	private HSSFCell getCellAt( int r, int c ) {
		HSSFRow row = sheet.getRow( r );
		if( row == null ) {
			row = sheet.createRow( r );
		}
		HSSFCell cell = row.getCell( c );
		if( cell == null ) {
			cell = row.createCell( c );
		}
		return cell;
	}

	private String getCellValue( HSSFCell c ) {
		if( c == null ) { return null; }
		switch( c.getCellType() ) {
		case Cell.CELL_TYPE_STRING:
			return c.getStringCellValue();
		case Cell.CELL_TYPE_NUMERIC:
			return c.getNumericCellValue() + "";
		case Cell.CELL_TYPE_BOOLEAN:
			return c.getBooleanCellValue() + "";
		default:
			return null;
		}
	}

	private Date getDateValueAt( int r, int c ) {
		HSSFRow row = sheet.getRow( r );
		if( row == null ) { return null; }
		HSSFCell cell = row.getCell( c );
		if( cell == null ) { return null; }
		return cell.getDateCellValue();
	}

	private String getCellValueAt( int r, int c ) {
		HSSFRow row = sheet.getRow( r );
		if( row == null ) { return null; }
		HSSFCell cell = row.getCell( c );
		if( cell == null ) { return null; }
		return getCellValue( cell );
	}

	private void setCellValueAt( int r, int c, String value ) {
		HSSFCell cell = getCellAt( r, c );
		cell.setCellValue( value );
	}

	/*
	 * private void setCellValueAt( int r, int c, double value ) { HSSFCell cell
	 * = getCellAt( r, c ); cell.setCellValue( value ); }
	 * 
	 * private void setCellValueAt( int r, int c, int value ) { HSSFCell cell =
	 * getCellAt( r, c ); cell.setCellValue( value ); }
	 * 
	 * private void setCellValueAt( int r, int c, boolean value ) { HSSFCell
	 * cell = getCellAt( r, c ); cell.setCellValue( value ); }
	 */

	private void setCellValueAt( int r, int c, Date value ) {
		HSSFCell cell = getCellAt( r, c );
		cell.setCellStyle( dateStyle );
		cell.setCellValue( value );
	}

	private void deleteRow( int r ) {
		HSSFRow row = sheet.getRow( r );
		sheet.removeRow( row );
		insertionPoint = sheet.getLastRowNum() + 1;
	}

	private void loadSheet() {
		sheet = workbook.getSheet( "DataDump" );
		if( sheet == null ) {
			sheet = workbook.createSheet( "DataDump" );
			// insert headers
			int c = 0;
			for( String h: headerLabels ) {
				setCellValueAt( 0, c++, h );
			}
		}
	}

	private void loadWorkbook() {
		try {
			FileInputStream fileIn = new FileInputStream( file );
			workbook = new HSSFWorkbook( fileIn );
			fileIn.close();
		} catch( FileNotFoundException e ) {
			System.out
					.println( "File: '"
							+ file.getPath()
							+ "' not found. File will be created on data insertion.\nYou can choose a different file before inserting any data." );
			// create new workbook
			workbook = new HSSFWorkbook();
		} catch( IOException e ) {
			e.printStackTrace();
		}
		loadSheet();
	}

	/**
	 * @param args
	 */
	public static void main( String[] args ) {
		ExcelEntry e = new ExcelEntry();
		e.openExcel();
		e.createGUI();

		e.setVisible( true );
	}

	private void openExcel() {
		loadProperties();
		String filename = properties.getProperty( "ExcelFile" );
		file = new File( filename );
		loadWorkbook();

		dateStyle = workbook.createCellStyle();
		dateStyle.setDataFormat( (short) 0xe );

		insertionPoint = sheet.getLastRowNum() + 1;
	}

	private void insertRecord() {
		for( int i = 0; i < DATATYPES.length; i++ ) {
			if( DATATYPES[i] == 2 ) {
				setCellValueAt( insertionPoint, i,
						((JDateChooser) entryFields[i]).getDate() );
			} else if( DATATYPES[i] == 1 ) {
				setCellValueAt( insertionPoint, i,
						((JTextField) entryFields[i]).getText() );
			}
		}
		insertionPoint++;
		saveWorkbook();
	}

	private void updateRecord() {
		if( srchInd.size() < 1 ) return;
		int rec = srchInd.get( cursor );
		for( int i = 0; i < HEADERS.length; i++ ) {
			if( DATATYPES[i] == 2 ) {
				setCellValueAt( rec, i,
						((JDateChooser) recordFields[i]).getDate() );
			} else if( DATATYPES[i] == 1 ) {
				setCellValueAt( rec, i,
						((JTextField) recordFields[i]).getText() );
			}
		}
		saveWorkbook();
	}

	private void deleteRecord() {
		if( srchInd.size() > 0 ) {
			int r = srchInd.get( cursor );
			deleteRow( r );
			srchInd.remove( cursor );
			populateSchedule();
			saveWorkbook();
		}
	}

	private void createGUIthread() {
		setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE );
		addWindowListener( this );
		setTitle( "Excel Entry Form" );

		JPanel entryPane = new JPanel( new SpringLayout() );

		String dateFormat = properties.getProperty( "DateFormat" );
		if( dateFormat == null ) {
			dateFormat = "MM/dd/yyyy";
			properties.setProperty( "DateFormat", "MM/dd/yyyy" );
		}
		for( int i = 0; i < HEADERS.length; i++ ) {
			JLabel l1 = new JLabel( headerLabels[i] );
			if( DATATYPES[i] == 2 ) {
				entryFields[i] = new JDateChooser( new Date(), dateFormat );
			} else if( DATATYPES[i] == 1 ) {
				entryFields[i] = new JTextField( 15 );
			}
			l1.setLabelFor( entryFields[i] );
			entryPane.add( l1 );
			entryPane.add( entryFields[i] );
		}
		SpringUtilities.makeCompactGrid( entryPane, entryFields.length, 2, 6,
				6, 6, 6 );

		// insert button
		JPanel entryPanel = new JPanel( new BorderLayout() );
		entryPanel.setName( "Entry" );
		JButton btn = new JButton( "Insert into excel" );
		btn.setActionCommand( "insert" );
		btn.addActionListener( this );
		entryPanel.add( entryPane, BorderLayout.CENTER );
		entryPanel.add( btn, BorderLayout.SOUTH );

		// search tab
		JPanel srhpane = new JPanel();
		JPanel filterComponents = new JPanel( new SpringLayout() );
		srhpane.add( filterComponents );
		srhpane.setName( "Search" );

		// filter by date
		filterByDate = new JCheckBox();
		filterByDate.setSelected( true );
		filterComponents.add( filterByDate );
		srhcombo = new JComboBox<>();
		for( int i = 0; i < DATATYPES.length; i++ ) {
			if( DATATYPES[i] == 2 ) {
				srhcombo.addItem( headerLabels[i] );
			}
		}
		filterComponents.add( srhcombo );
		filterComponents.add( new JLabel( "Date Diff:" ) );
		srhdiff = new JTextField( 15 );
		srhdiff.setText( "5" );
		filterComponents.add( srhdiff );

		// filter by text
		filterByText = new JCheckBox();
		filterByText.setSelected( false );
		filterComponents.add( filterByText );
		srhtextcombo = new JComboBox<>();
		for( int i = 0; i < DATATYPES.length; i++ ) {
			if( DATATYPES[i] == 1 ) {
				srhtextcombo.addItem( headerLabels[i] );
			}
		}
		filterComponents.add( srhtextcombo );
		filterComponents.add( new JLabel( "Search for:" ) );
		srhText = new JTextField( 15 );
		filterComponents.add( srhText );

		SpringUtilities.makeCompactGrid( filterComponents, 2, 4, 6, 6, 6, 6 );

		// view record sheet
		JPanel spane = new JPanel( new BorderLayout() );
		spane.setName( "Records" );
		JPanel spanef = new JPanel( new SpringLayout() );
		spane.add( spanef, BorderLayout.CENTER );

		JLabel lbl = new JLabel( "Record" );
		spanef.add( lbl );
		recordLabel = new JLabel();
		spanef.add( recordLabel );

		for( int i = 0; i < HEADERS.length; i++ ) {
			JLabel l1 = new JLabel( headerLabels[i] );
			if( DATATYPES[i] == 2 ) {
				recordFields[i] = new JDateChooser( new Date(), dateFormat );
			} else if( DATATYPES[i] == 1 ) {
				recordFields[i] = new JTextField( 15 );
			}
			l1.setLabelFor( recordFields[i] );
			spanef.add( l1 );
			spanef.add( recordFields[i] );
		}
		SpringUtilities.makeCompactGrid( spanef, entryFields.length + 1, 2, 6,
				6, 6, 6 );
		// navigation buttons
		JButton recDelete = new JButton( "Delete" );
		recDelete.setActionCommand( "delete" );
		recDelete.addActionListener( this );
		recUpdate = new JButton( "Update" );
		recUpdate.setActionCommand( "update" );
		recUpdate.addActionListener( this );
		navFirst = new JButton( "First" );
		navFirst.setActionCommand( "first" );
		navFirst.addActionListener( this );
		navPrev = new JButton( "Previous" );
		navPrev.setActionCommand( "prev" );
		navPrev.addActionListener( this );
		navNext = new JButton( "Next" );
		navNext.setActionCommand( "next" );
		navNext.addActionListener( this );
		navLast = new JButton( "Last" );
		navLast.setActionCommand( "last" );
		navLast.addActionListener( this );
		JPanel nav = new JPanel( new GridLayout( 0, 5 ) );
		// "Delete" not working because of the way rows are stored and deleted
		// in excel.
		// nav.add( recDelete );
		nav.add( recUpdate );
		nav.add( navFirst );
		nav.add( navPrev );
		nav.add( navNext );
		nav.add( navLast );
		spane.add( nav, BorderLayout.SOUTH );

		// view list pane
		JPanel lpane = new JPanel();
		lpane.setName( "List" );
		listPane = new JPanel( new SpringLayout() );
		lpane.add( listPane );

		// options pane
		JPanel opt = new JPanel();
		JPanel opt1 = new JPanel( new SpringLayout() );
		opt.add( opt1 );
		opt.setName( "Options" );

		// excel file
		lbl = new JLabel( "Excel file" );
		opt1.add( lbl );
		FileChooser filepath = new FileChooser( file );
		filepath.addActionListener( this );
		filepath.setActionCommand( "file" );
		opt1.add( filepath );

		// date format
		lbl = new JLabel( "Date format" );
		opt1.add( lbl );
		JComboBox< String > lstDateFormat = new JComboBox< String >();
		lstDateFormat.addItem( "MM/dd/yyyy" );
		lstDateFormat.addItem( "dd/MM/yyyy" );
		lstDateFormat.addItem( "dd/MMM/yyyy" );
		lstDateFormat.addItem( "MMM dd, yyyy" );
		lstDateFormat.addItem( "EEEE MMM dd, yyyy" );
		lstDateFormat.setSelectedItem( properties.getProperty( "DateFormat" ) );
		lstDateFormat.setActionCommand( "dateformat" );
		lstDateFormat.addActionListener( this );
		opt1.add( lstDateFormat );

		// field labels
		JLabel fld = new JLabel( "Field" );
		opt1.add( fld );
		fld = new JLabel( "Set new label (requires restart)" );
		opt1.add( fld );
		lblCombo = new JComboBox<>();
		for( int i = 0; i < DATATYPES.length; i++ ) {
			lblCombo.addItem( headerLabels[i] );
		}
		lblCombo.setActionCommand( "headerlabel" );
		lblCombo.addActionListener( this );
		opt1.add( lblCombo );
		txtLabel = new JTextField( headerLabels[0] );
		txtLabel.addFocusListener( this );
		opt1.add( txtLabel );

		SpringUtilities.makeCompactGrid( opt1, 4, 2, 6, 6, 6, 6 );

		// layout
		JTabbedPane tbdpane = new JTabbedPane();
		tbdpane.addChangeListener( this );
		tbdpane.add( entryPanel );
		tbdpane.add( srhpane );
		tbdpane.add( spane );
		tbdpane.add( lpane );
		tbdpane.add( opt );

		add( tbdpane );

		pack();
	}

	private void createGUI() {

		SwingUtilities.invokeLater( new Runnable() {

			@Override
			public void run() {
				createGUIthread();
			}
		} );
	}

	private void loadProperties() {
		properties = new Properties();
		try {
			properties.load( new FileInputStream( "properties" ) );
			for( int i = 0; i < HEADERS.length; i++ ) {
				headerLabels[i] = properties.getProperty( HEADERS[i] );
			}
		} catch( IOException e ) {
			properties.setProperty( "ExcelFile", "workbook.xls" );
			for( int i = 0; i < HEADERS.length; i++ ) {
				properties.setProperty( HEADERS[i], HEADERS[i] );
				headerLabels[i] = HEADERS[i];
			}
		}
	}

	private void resetEntryFields() {
		Date d = new Date();
		for( int i = 0; i < entryFields.length; i++ ) {
			if( DATATYPES[i] == 2 )
				((JDateChooser) entryFields[i]).setDate( d );
			else if( DATATYPES[i] == 1 )
				((JTextField) entryFields[i]).setText( null );
		}
	}

	private void saveWorkbook() {
		try {
			FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
			workbook.write( fileOut );
			fileOut.close();
			resetEntryFields();
		} catch( IOException e1 ) {
			e1.printStackTrace();
		}
	}

	@Override
	public void actionPerformed( ActionEvent e ) {
		String cmd = e.getActionCommand();
		switch( cmd ) {
		case "headerlabel":
			txtLabel.setText( headerLabels[lblCombo.getSelectedIndex()] );
			break;
		case "file":
			file = ((FileChooser) e.getSource()).getFile();
			properties.setProperty( "ExcelFile", file.getPath() );
			loadWorkbook();
			break;
		case "dateformat":
			@SuppressWarnings("unchecked")
			JComboBox< String > cb = (JComboBox< String >) e.getSource();
			String format = (String) cb.getSelectedItem();
			setDateFormat( format );
			properties.setProperty( "DateFormat", format );
			break;
		case "insert":
			insertRecord();
			break;
		case "delete":
			deleteRecord();
			break;
		case "update":
			updateRecord();
			break;
		case "first":
			if( srchInd.size() > 0 ) {
				cursor = 0;
				populateSchedule();
			}
			break;
		case "prev":
			if( cursor > 0 ) {
				cursor--;
				populateSchedule();
			}
			break;
		case "next":
			if( srchInd.size() > cursor + 1 ) {
				cursor++;
				populateSchedule();
			}
			break;
		case "last":
			if( srchInd.size() > 0 ) {
				cursor = srchInd.size() - 1;
				populateSchedule();
			}
			break;
		}
	}

	private void setDateFormat( String format ) {
		for( int i = 0; i < HEADERS.length; i++ ) {
			if( DATATYPES[i] == 2 ) {
				((JDateChooser) entryFields[i]).setDateFormatString( format );
				((JDateChooser) recordFields[i]).setDateFormatString( format );
			}
		}
	}

	private void filterByDate() {
		ArrayList< Integer > temp = new ArrayList<>();

		if( filterByDate.isSelected() ) {
			Date td = new Date();
			int indextosearch = getDateFieldIndex( srhcombo.getSelectedIndex() );
			int datediff = Integer.parseInt( srhdiff.getText() );
			for( int i: srchInd ) {
				Date d = getDateValueAt( i, indextosearch );
				int diff = Math
						.abs( (int) ((d.getTime() - td.getTime()) / MILLISECS_PER_DAY) );
				if( diff <= datediff ) {
					temp.add( i );
				}
			}
			srchInd.clear();
			srchInd.addAll( temp );
		}
	}

	private int getDateFieldIndex( int srchIndex ) {
		for( int i = 0, j = 0; i < DATATYPES.length; i++ ) {
			if( DATATYPES[i] != 2 ) {
				continue;
			}
			if( j == srchIndex ) { return i; }
			j++;
		}
		return -1;
	}

	private int getTextFieldIndex( int srchIndex ) {
		for( int i = 0, j = 0; i < DATATYPES.length; i++ ) {
			if( DATATYPES[i] != 1 ) {
				continue;
			}
			if( j == srchIndex ) { return i; }
			j++;
		}
		return -1;
	}

	private void filterByText() {
		ArrayList< Integer > temp = new ArrayList<>();

		if( filterByText.isSelected() ) {
			int indextosearch = getTextFieldIndex( srhtextcombo
					.getSelectedIndex() );
			for( int i: srchInd ) {
				String txt = getCellValueAt( i, indextosearch );
				int ind = txt.indexOf( srhText.getText() );
				if( ind >= 0 ) {
					temp.add( i );
				}
			}
			srchInd.clear();
			srchInd.addAll( temp );
		}
	}

	private void searchRecords() {
		int rows = sheet.getLastRowNum() + 1;
		srchInd.clear();
		// populate all rows if no search criteria
		for( int i = 1; i < rows; i++ ) {
			srchInd.add( i );
		}

		if( rows > 1 ) {
			filterByDate();
			filterByText();
		}
		cursor = 0;
	}

	private void populateList() {
		listPane.removeAll();
		if( srchInd.size() > 0 ) {
			String format = properties.getProperty( "DateFormat" );
			// add headers
			for( int f = 0; f < HEADERS.length; f++ ) {
				JLabel lbl = new JLabel( headerLabels[f] );
				listPane.add( lbl );
			}
			for( int index: srchInd ) {
				for( int f = 0; f < HEADERS.length; f++ ) {
					if( DATATYPES[f] == 1 ) {
						JTextField txt = new JTextField( getCellValueAt( index,
								f ) );
						listPane.add( txt );
					} else if( DATATYPES[f] == 2 ) {
						JDateChooser txt = new JDateChooser( getDateValueAt(
								index, f ) );
						txt.setDateFormatString( format );
						listPane.add( txt );
					}
				}
			}

			SpringUtilities.makeCompactGrid( listPane, srchInd.size() + 1,
					HEADERS.length, 6, 6, 2, 6 );
			pack();
		} else {
			JLabel lbl = new JLabel(
					"No Records to display. Please expand your Search." );
			listPane.add( lbl );
			pack();
		}
	}

	private void populateSchedule() {
		if( srchInd.size() > 0 ) {
			recordLabel.setText( (cursor + 1) + " of " + srchInd.size() );
			int row = srchInd.get( cursor );
			for( int i = 0; i < HEADERS.length; i++ ) {
				if( DATATYPES[i] == 1 ) {
					((JTextField) recordFields[i]).setText( getCellValueAt(
							row, i ) );
				} else if( DATATYPES[i] == 2 ) {
					Date d = getDateValueAt( row, i );
					((JDateChooser) recordFields[i]).setDate( d );
				}
			}
		} else {
			recordLabel.setText( "0 of 0" );
			for( int i = 0; i < HEADERS.length; i++ ) {
				if( DATATYPES[i] == 1 ) {
					((JTextField) recordFields[i]).setText( "NA" );
				} else if( DATATYPES[i] == 2 ) {
					((JDateChooser) recordFields[i]).setDate( null );
				}
			}
		}
	}

	@Override
	public void stateChanged( ChangeEvent e ) {
		JTabbedPane src = (JTabbedPane) e.getSource();
		if( src.getSelectedIndex() == 2 ) {
			searchRecords();
			populateSchedule();
		} else if( src.getSelectedIndex() == 3 ) {
			searchRecords();
			populateList();
		}
	}

	@Override
	public void windowClosing( WindowEvent we ) {
		try {
			properties.store( new FileOutputStream( "properties" ),
					"Properties file for ExcelEntry" );
		} catch( IOException e ) {
			e.printStackTrace();
		}
	}

	@Override
	public void focusLost( FocusEvent fe ) {
		JTextField txt = (JTextField) fe.getSource();
		int sel = lblCombo.getSelectedIndex();

		headerLabels[sel] = txt.getText();
		properties.setProperty( HEADERS[sel], txt.getText() );
	}

	@Override
	public void windowActivated( WindowEvent arg0 ) {}

	@Override
	public void windowClosed( WindowEvent arg0 ) {}

	@Override
	public void windowDeactivated( WindowEvent arg0 ) {}

	@Override
	public void windowDeiconified( WindowEvent arg0 ) {}

	@Override
	public void windowIconified( WindowEvent arg0 ) {}

	@Override
	public void windowOpened( WindowEvent arg0 ) {}

	@Override
	public void focusGained( FocusEvent arg0 ) {}
}